/** FASSIT (Forms of Selective Attention in Intelligent Transportation Systems)
 * Computational Creativity and Digital Media
 * Cognitive and Media Systems Group
 * Centre for Informatics and Systems of the University of Coimbra (CISUC)
 *
 * Copyright (c) 2010-2013 University of Coimbra, Portugal
 * All rights reserved.
 */

package agent.DBManager;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import poi.Info.POI;
import poi.Info.POIwithSurprise;
import poi.Surprise.SurpriseDaysOff;
import poi.Surprise.SurprisePrice;
import poi.Surprise.SurpriseSchedule;
import agent.Context.Context;
import agent.Metrics.Surprise.FirstPOI;
import agent.Metrics.Surprise.PastSurpriseValues;

import com.mysql.jdbc.Connection;

import functionsVariables.MySQLConnection;

/**
 * In this class defines all the low level methods to communicate with the mysql
 * database
 * 
 * @author Hernani Costa
 * @version 0.2 date: 27/01/2013
 */
public class AgentDBConnection {

	private MySQLConnection dbconnection;
	private Connection connection;

	/**
	 * ------------------------------------------------------------------------
	 * Constructor class
	 * ------------------------------------------------------------------------
	 */
	public AgentDBConnection() {
		dbconnection = MySQLConnection.getInstance();
	}

	/**
	 * ------------------------------------------------------------------------
	 * Deletes the stored Agent Knowledge from the tables 'ex3_agentX_knowledge'
	 * and 'ex3_agentX_surpriseValues'
	 * ------------------------------------------------------------------------
	 */
	public void deleteAgentKnowledge() {
		try {
			connection = dbconnection.openConnection();
			String query1 = "DELETE FROM  ex3_agentX_knowledge";

			PreparedStatement s = connection.prepareStatement(query1);
			s.executeUpdate();

			String query2 = "DELETE FROM  ex3_agentX_surpriseValues";
			s = connection.prepareStatement(query2);
			s.executeUpdate();
			s.close();
			dbconnection.closeConnection();
		} catch (SQLException e) {
			e.printStackTrace();
			dbconnection.closeConnection();
		}
		// CREATE TABLE recipes_new LIKE production.recipes;
		// INSERT recipes_new SELECT * FROM production.recipes;
	}

	/**
	 * Saves all the POIs visited by the agent (in a specific Context) and the
	 * resulted Surprise values for each POI
	 * 
	 * @param pois
	 *            - POIs visited by the Agent
	 * @param context
	 *            - Context
	 * @return true if successfully saved, false if not
	 */
	public boolean savePOIs(ArrayList<POI> pois, Context context) {
		int poiID, poiCatID, poiSubCatID;
		double distanceBetweenUserPOI;
		String poiCatName, poiSubCatName, poiName, poiPrice, poiSchedule, poiDaysOff, agentDistanceTOpoi_string;
		double poiLat, poiLng;
		String agentTimeOfDay = context.getTimeOfDay();
		String agentDayOfWeek = context.getDayOfWeek();
		int runID = context.getRunID();
		String agentGoal = context.getGoal();
		double surpriseTOcategory, surpriseTOsubCategory, surpriseTOprice, surpriseTOdayOff, surpriseTOschedule, surpriseAverage;

		connection = dbconnection.openConnection();
		try {
			PreparedStatement s = null;

			for (POI poi : pois) {
				poiID = poi.getPoiID();
				poiCatID = poi.getCategory().getCategoryID();
				poiSubCatID = poi.getSubCategory().getSubCategoryID();
				poiCatName = poi.getCategory().getCategoryName();
				poiSubCatName = poi.getSubCategory().getSubCategoryName();
				agentDistanceTOpoi_string = poi.getAgentDistanceTOpoi_string();
				distanceBetweenUserPOI = context.getLocation().getGeoPoint().getDistanceMeters(
						poi.getLocation().getGeoPoint());
				poiName = poi.getPoiName();
				poiPrice = poi.getPrice().getAveragePrice();
				poiSchedule = poi.getDaysOn().getStringDaysOn();
				poiDaysOff = poi.getDaysOff().getStringDaysOff();
				poiLat = poi.getLocation().getLatitude();
				poiLng = poi.getLocation().getLongitude();
				surpriseTOcategory = poi.getSurpriseTOcategory();
				surpriseTOsubCategory = poi.getSurpriseTOsubCategory();
				surpriseTOprice = poi.getSurpriseTOprice();
				surpriseTOdayOff = poi.getSurpriseTOdayOff();
				surpriseTOschedule = poi.getSurpriseTOschedule();
				surpriseAverage = poi.getSurpriseAverage();

				s = connection
						.prepareStatement("INSERT INTO ex3_agentX_knowledge ("
								+ "poiID, "
								+ "poiName,"
								+ "poiLat,"
								+ "poiLng,"
								+ "poiPrice,"
								+ "poiSchedule,"
								+ "poiDayOff,"
								+ "poiCatID, "
								+ "poiCatName,"
								+ "poiSubCatID, "
								+ "poiSubCatName, "
								+ "agentDistanceTOpoi_string,"
								+ "agentDistanceTOpoi_int,"
								+ "agentTimeOfDay,"
								+ "agentDayOfWeek,"
								+ "agentGoal,"
								+ "surpriseTOcategory,"
								+ "surpriseTOsubCategory,"
								+ "surpriseTOprice,"
								+ "surpriseTOdayOff,"
								+ "surpriseTOschedule,"
								+ "surpriseAverage,"
								+ "runID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

				s.setInt(1, poiID);
				s.setString(2, poiName);
				s.setDouble(3, poiLat);
				s.setDouble(4, poiLng);
				s.setString(5, poiPrice);
				s.setString(6, poiSchedule);
				s.setString(7, poiDaysOff);
				s.setInt(8, poiCatID);
				s.setString(9, poiCatName);
				s.setInt(10, poiSubCatID);
				s.setString(11, poiSubCatName);
				s.setString(12, agentDistanceTOpoi_string);
				s.setDouble(13, distanceBetweenUserPOI);
				s.setString(14, agentTimeOfDay);
				s.setString(15, agentDayOfWeek);
				s.setString(16, agentGoal);
				s.setDouble(17, surpriseTOcategory);
				s.setDouble(18, surpriseTOsubCategory);
				s.setDouble(19, surpriseTOprice);
				s.setDouble(20, surpriseTOdayOff);
				s.setDouble(21, surpriseTOschedule);
				s.setDouble(22, surpriseAverage);
				s.setInt(23, runID);

				s.executeUpdate();
				s.close();
			}
			dbconnection.closeConnection();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			dbconnection.closeConnection();
			return false;
		}

	}

	/**
	 * Saves the Surprise values for all the parameters (Price, DaysOff and
	 * Schedule)
	 * 
	 * @param poiWithSurprise
	 *            - POI with the Surprise values
	 * @param context
	 *            - Context
	 * @return true if successfully saved, false if not
	 */
	public boolean saveSurpriseValues(POIwithSurprise poiWithSurprise,
			Context context) {
		System.out.println("POI TO BE STORED:" + poiWithSurprise.to_String());
		int poiID;
		int runID = context.getRunID();
		String agentGoal = context.getGoal();
		double price_cheap, price_average, price_expensive, schedule_morning, schedule_afternoon, schedule_night, dayoff_sunday, dayoff_monday, dayoff_tuesday, dayoff_wednesday, dayoff_thursday, dayoff_friday, dayoff_saturday;
		POI poi = null;

		connection = dbconnection.openConnection();
		try {
			PreparedStatement s = null;

			// for (POIwithSurprise poiSurprise : poisWithSurprise) {
			poi = poiWithSurprise.getPoi();

			poiID = poi.getPoiID();
			System.out.println("POI id:" + poiID);
			price_cheap = poiWithSurprise.getSurprisePrice().getCheap();
			price_average = poiWithSurprise.getSurprisePrice().getAverage();
			price_expensive = poiWithSurprise.getSurprisePrice().getExpensive();
			schedule_morning = poiWithSurprise.getSurpriseSchedule()
					.getMorning();
			System.out.println("Schedule: "+schedule_morning);
			schedule_afternoon = poiWithSurprise.getSurpriseSchedule()
					.getAfternoon();
			schedule_night = poiWithSurprise.getSurpriseSchedule().getNight();
			dayoff_sunday = poiWithSurprise.getSurpriseDayOff().getSunday();
			dayoff_monday = poiWithSurprise.getSurpriseDayOff().getMonday();
			dayoff_tuesday = poiWithSurprise.getSurpriseDayOff().getTuesday();
			dayoff_wednesday = poiWithSurprise.getSurpriseDayOff()
					.getWednesday();
			dayoff_thursday = poiWithSurprise.getSurpriseDayOff().getThursday();
			dayoff_friday = poiWithSurprise.getSurpriseDayOff().getFriday();
			dayoff_saturday = poiWithSurprise.getSurpriseDayOff().getSaturday();

			s = connection
					.prepareStatement("INSERT INTO ex3_agentX_surpriseValues ("
							+ "poiID, "
							+ "price_cheap,"
							+ "price_average,"
							+ "price_expensive,"
							+ "schedule_morning,"
							+ "schedule_afternoon,"
							+ "schedule_night,"
							+ "dayoff_sunday, "
							+ "dayoff_monday,"
							+ "dayoff_tuesday, "
							+ "dayoff_wednesday, "
							+ "dayoff_thursday,"
							+ "dayoff_friday,"
							+ "dayoff_saturday,"
							+ "runID,"
							+ "agentGoal) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

			s.setInt(1, poiID);
			s.setDouble(2, price_cheap);
			s.setDouble(3, price_average);
			s.setDouble(4, price_expensive);
			s.setDouble(5, schedule_morning);
			s.setDouble(6, schedule_afternoon);
			s.setDouble(7, schedule_night);
			s.setDouble(8, dayoff_sunday);
			s.setDouble(9, dayoff_monday);
			s.setDouble(10, dayoff_tuesday);
			s.setDouble(11, dayoff_wednesday);
			s.setDouble(12, dayoff_thursday);
			s.setDouble(13, dayoff_friday);
			s.setDouble(14, dayoff_saturday);
			s.setDouble(15, runID);
			s.setString(16, agentGoal);

			s.executeUpdate();
			s.close();
			// }
			dbconnection.closeConnection();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			dbconnection.closeConnection();
			return false;
		}
	}

	/**
	 * For a specific Goal, returns all the Surprise values for all the POIs
	 * visited by the Agent in the past
	 * 
	 * @param context
	 *            - Current context
	 * @return all the Surprise values for all the POIs visited by the Agent in
	 *         the past
	 */
	@SuppressWarnings("null")
	public PastSurpriseValues getSurpriseValuesForGoal(Context context) {
		boolean flag = true;
		Statement stt = null;
		ResultSet rs = null;
		PastSurpriseValues surprise = new PastSurpriseValues();
		SurpriseDaysOff sDayOff = null;
		SurprisePrice sPrice = null;
		SurpriseSchedule sSchedule = null;
		double price_cheap, price_average, price_expensive, schedule_morning, schedule_afternoon, schedule_night, dayoff_sunday, dayoff_monday, dayoff_tuesday, dayoff_wednesday, dayoff_thursday, dayoff_friday, dayoff_saturday;

		int id, poiID, runID;
		String agentGoal = "";

		try {
			connection = dbconnection.openConnection();
			stt = connection.createStatement();
			rs = stt.executeQuery("SELECT * FROM ex3_agentX_surpriseValues WHERE agentGoal='"
					+ context.getGoal() + "'");

			while (rs.next()) {
				id = rs.getInt(1);
				poiID = rs.getInt(2);
				price_cheap = rs.getDouble(3);
				price_average = rs.getDouble(4);
				price_expensive = rs.getDouble(5);
				schedule_morning = rs.getDouble(6);
				schedule_afternoon = rs.getDouble(7);
				schedule_night = rs.getDouble(8);
				dayoff_sunday = rs.getDouble(9);
				dayoff_monday = rs.getDouble(10);
				dayoff_tuesday = rs.getDouble(11);
				dayoff_wednesday = rs.getDouble(12);
				dayoff_thursday = rs.getDouble(13);
				dayoff_friday = rs.getDouble(14);
				dayoff_saturday = rs.getDouble(15);
				runID = rs.getInt(16);
				agentGoal = rs.getString(17);

				sDayOff = new SurpriseDaysOff(dayoff_sunday, dayoff_monday,
						dayoff_tuesday, dayoff_wednesday, dayoff_thursday,
						dayoff_friday, dayoff_saturday);
				sPrice = new SurprisePrice(price_cheap, price_average,
						price_expensive);
				sSchedule = new SurpriseSchedule(schedule_morning,
						schedule_afternoon, schedule_night);
				surprise.addSurprise(sDayOff, sPrice, sSchedule);
				flag = false;

			}
			dbconnection.closeConnection();
		} catch (SQLException e) {
			e.printStackTrace();
			dbconnection.closeConnection();
		}
		// In the training process we do not have yet any entry in the table
		// 'ex3_agentX_surpriseValues', so we need to create a the 'FIRSTPOI'
		if (flag) {
			FirstPOI firstPOI = new FirstPOI();
			System.out.println("PRIMERO POI: " + firstPOI.to_String());
			surprise.addSurprise(firstPOI.getSurpriseDaysOff(),
					firstPOI.getSurprisePrice(), firstPOI.getSurpriseSchedule());

		}

		return surprise;
	}

	


	/**
	 * ------------------------------------------------------------------------
	 * Used to provide an example that exercises the functionality of the class.
	 * ------------------------------------------------------------------------
	 * 
	 * @param args
	 */
	public static void main(String args[]) {
		// creating a POI
		// ArrayList<POI> pois = new ArrayList<POI>();
		// masterAgent.DBmanager.DBConnection s = new
		// masterAgent.DBmanager.DBConnection();
		// pois.add(s.getPOI(2114795));
		// pois.add(s.getPOI(7540801));
		// Context c = new Context(new Location(8.2, 0.3), "morning", "cenas",
		// new Goal("party"), 100, 1);
		//
		// AgentDBConnection agent = new AgentDBConnection();
		// // agent.savePOIs(pois, c);
		//
		// agent.getPOIsNearLocation(new Goal("party"));

	}

	// CREATE TABLE `POIsDB`.`ex3_agentX_surpriseValues` (
	// `id` INT NOT NULL AUTO_INCREMENT ,
	// `poiID` INT NOT NULL ,
	// `price_cheap` DOUBLE NULL ,
	// `price_average` DOUBLE NULL ,
	// `price_expensive` DOUBLE NULL ,
	// `schedule_morning` DOUBLE NULL ,
	// `schedule_afternoon` DOUBLE NULL ,
	// `schedule_night` DOUBLE NULL ,
	// `dayoff_sunday` DOUBLE NULL ,
	// `dayoff_monday` DOUBLE NULL ,
	// `dayoff_tuesday` DOUBLE NULL ,
	// `dayoff_wednesday` DOUBLE NULL ,
	// `dayoff_thusday` DOUBLE NULL ,
	// `dayoff_friday` DOUBLE NULL ,
	// `dayoff_saturday` DOUBLE NULL ,
	// `runID` INT NULL ,
	// PRIMARY KEY ( `id` )
	// ) ENGINE = MYISAM ;

}
